﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Linq;
using System.Windows.Forms;
using DevExpress.XtraEditors;
using VNPT.DataExport.DbIn;

namespace VNPT.DataExport
{
    public partial class fMain : DevExpress.XtraEditors.XtraForm
    {
        public fMain()
        {
            InitializeComponent();
        }
        VNPT.Utility.Profile.Xml xml = new Utility.Profile.Xml();
        VNPT.Data.DbNetData Db;

        void ThietLap_DuLieu(bool ShowSetting = true)
        {
            if (comboBoxEdit1.EditValue != null)
            {

                var pro = comboBoxEdit1.Text;
                string template = "";
                var _dbType = Data.DataProvider.SqlClient;
                switch (pro)
                {
                    case "Oracle":
                        _dbType = Data.DataProvider.OracleClient;
                        template = "Data Source=DAKLAK_6;User Id=VinhThuyen;Password=123456;Integrated Security=no;";

                        break;
                    case "SQL Server":
                        _dbType = Data.DataProvider.SqlClient;
                        template = @"Server=(local)\SQLEXPRESS; Database=myDataBase; User Id=sa; Password=123456;";

                        break;
                    case "SQL Server CE":
                        _dbType = Data.DataProvider.SqlServerCE;
                        template = "Data Source=\"{0}\";Persist Security Info=False;";

                        break;
                    case "Microsoft Access":
                        _dbType = Data.DataProvider.OleDb;
                        template = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"{0}\";User Id=admin;Password=;";

                        break;
                    case "Microsoft Excel":
                        _dbType = Data.DataProvider.OleDb;
                        template = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=1\";";

                        break;
                    case "MySql":
                        _dbType = Data.DataProvider.MySql;
                        template = "Server=localhost;Database=myDataBase;Uid=root;Pwd=123456;";

                        break;
                    case "Visual FoxPro":
                        _dbType = Data.DataProvider.OleDb;
                        template = "Provider=vfpoledb;Data Source='{0}';Collating Sequence=machine;";
                        break;
                    case "SQLite":
                        _dbType = Data.DataProvider.SQLite;
                        template = "Data Source='{0}';Version=3;";
                        break;
                    case "VistaDB":
                        _dbType = Data.DataProvider.VistaDB; //*.vdb4
                        template = "Data Source='{0}';Open Mode=ExclusiveReadWrite;";

                        break;
                    case "Firebird":
                        template = @"User=SYSDBA;Password=masterkey;Database=SampleDatabase.fdb;DataSource=localhost;Port=3050;Dialect=3;Charset=NONE;Role=;Connection lifetime=15;Pooling=true;MinPoolSize=0;MaxPoolSize=50;Packet Size=8192;ServerType=0;";
                        _dbType = Data.DataProvider.Firebird;
                        break;
                    default:
                        template = "Data Source=DAKLAK_6;User Id=VinhThuyen;Password=123456;Integrated Security=no;";
                        _dbType = Data.DataProvider.OracleClient;
                        break;
                }

                string cnnstring = "";

                var t = (xml.GetValue("DataExport", _dbType.ToString()) ?? "").ToString();
                if (!string.IsNullOrWhiteSpace(t))
                {
                    template = t;
                }
                //Mo cua so chinh sua khong?
                if (ShowSetting)
                {
                    var f = new fGetConnectionString(_dbType, template);
                    cnnstring = f.ShowMe();
                    if (f.DialogResult == System.Windows.Forms.DialogResult.OK)
                    {
                        Db = new Data.DbNetData(cnnstring, _dbType);
                        Db.CloseConnectionOnError = false;
                    }
                }
                else
                {
                    Db = new Data.DbNetData(template, _dbType);
                    Db.CloseConnectionOnError = false;
                }
                
            }
            else
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Chưa chọn kiểu dữ liệu nguồn!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        public DataTable LayDuLieu(string SQL)
        {
            ThietLap_DuLieu(false);
            if (Db==null)
            {
                return null;
            }
            if (Db.Conn.State == ConnectionState.Closed)
            {
                Db.Open();
            }

            var dt = Db.GetDataTable(new Data.QueryCommandConfig(SQL));
            return dt;

        }

        string Query;
        void Xuat(string path, string Type)
        {
            var dt = LayDuLieu(Query);
            switch (Type)
            {
                case "Export to DBF (Foxpro)":
                    Spire.DataExport.DBF.DBFExport DBFExport = new Spire.DataExport.DBF.DBFExport();
                    DBFExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    DBFExport.DataTable = dt;
                    DBFExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                    DBFExport.FileName = path;
                    DBFExport.SaveToFile();

                    break;
                case "Export to PDF":
                    Spire.DataExport.PDF.PDFExport PDFExport = new Spire.DataExport.PDF.PDFExport();
                    PDFExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    PDFExport.DataTable = dt;
                    PDFExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                    PDFExport.SaveToFile(path);
                    break;
                case "Export to Word":
                    Spire.DataExport.RTF.RTFExport RTF = new Spire.DataExport.RTF.RTFExport();
                    RTF.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    RTF.DataTable = dt;
                    RTF.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                    RTF.FileName = path;
                    RTF.SaveToFile();
                    break;
                case "Export to Excel":
                    Spire.DataExport.XLS.CellExport cellExport = new Spire.DataExport.XLS.CellExport();
                    Spire.DataExport.XLS.WorkSheet worksheet1 = new Spire.DataExport.XLS.WorkSheet();
                    worksheet1.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    worksheet1.DataTable = dt;
                    worksheet1.StartDataCol = ((System.Byte)(0));
                    cellExport.Sheets.Add(worksheet1);
                    cellExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                    cellExport.SaveToFile(path);
                    break;
                case "Export to MS Access":
                    Spire.DataExport.Access.AccessExport accessExport = new Spire.DataExport.Access.AccessExport();
                    accessExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    accessExport.DataTable = dt;
                    accessExport.DatabaseName = path;
                    accessExport.TableName = string.Format("Datatable{0:ddMMyy_HHmmss}",DateTime.Now);
                    accessExport.SaveToFile();
                    break;
                case "Export to XML":
                    Spire.DataExport.XML.XMLExport xmlExport = new Spire.DataExport.XML.XMLExport();
                    xmlExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    xmlExport.DataTable = dt;
                    xmlExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                    xmlExport.FileName = path;
                    xmlExport.SaveToFile();
                    break;
                case "Export to HTML":
                    Spire.DataExport.HTML.HTMLExport HTMLExport = new Spire.DataExport.HTML.HTMLExport();
                    HTMLExport.DataSource = Spire.DataExport.Common.ExportSource.DataTable;
                    HTMLExport.DataTable = dt;
                    HTMLExport.ActionAfterExport = Spire.DataExport.Common.ActionType.OpenView;
                    HTMLExport.SaveToFile(path);
                    break;
                default:

                    break;
            }
        }

        private void btnChonNguon_Click(object sender, EventArgs e)
        {
            ThietLap_DuLieu();
        }

        private void btnXem_Click(object sender, EventArgs e)
        {
            if (this.bgWorker_Xem.IsBusy)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Chương trình đang chạy, vui lòng kiểm tra lại hoặc nhấn hủy!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }
            Query = mySQLEditor.Text;
            myGridView1.OptionsView.WaitAnimationOptions = DevExpress.XtraEditors.WaitAnimationOptions.Panel;
            myGridView1.ShowLoadingPanel();
            bgWorker_Xem.RunWorkerAsync();

        }

        string _path;
        string _typeExport;
        private void btnChuyen_Click(object sender, EventArgs e)
        {
            if (this.bgWorker_Xem.IsBusy)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Chương trình đang chạy, vui lòng kiểm tra lại hoặc nhấn hủy!", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
                return;
            }

            Query = mySQLEditor.Text;
            Spire.DataExport.DBF.DBFExport dbf = new Spire.DataExport.DBF.DBFExport();

            var source = comboBoxEdit2.Text;
            var filter = "All Files (*.*)|*.*";
            switch (source)
            {
                case "Export to DBF (Foxpro)":
                    filter = "Foxpro Files (*.dbf)|*.dbf|All Files (*.*)|*.*";
                    break;
                case "Export to PDF":
                    filter = "Adobe Pdf Files (*.pdf)|*.pdf|All Files (*.*)|*.*";
                    break;
                case "Export to Word":
                    filter = "Microsoft Word 2003 Files (*.doc)|*.doc|All Files (*.*)|*.*";
                    break;
                case "Export to Excel":
                    filter = "Microsoft Excel 2003 Files (*.xls)|*.xls|All Files (*.*)|*.*";
                    break;
                case "Export to MS Access":
                    filter = "Microsoft Excel Access Files (*.mdb)|*.mdb|All Files (*.*)|*.*";
                    break;
                case "Export to XML":
                    filter = "XML Files (*.xml)|*.xml|All Files (*.*)|*.*";
                    break;
                case "Export to HTML":
                    filter = "HTML Files (*.html)|*.html|All Files (*.*)|*.*";
                    break;
                default:
                    filter = "All Files (*.*)|*.*";
                    break;
            }
            using (var dlg = new SaveFileDialog())
            {
                dlg.Filter = filter;
                if (dlg.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    _path = dlg.FileName;
                    _typeExport = source;
                    myGridView1.ShowLoadingPanel();
                    bgWorker.RunWorkerAsync();
                }
            }
        }

        private void bgWorker_DoWork(object sender, DoWorkEventArgs e)
        {
            Xuat(_path, _typeExport);
        }

        private void bgWorker_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            myGridView1.HideLoadingPanel();
            if (e.Error == null)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show("Hoàn thành", "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Information);
            }
            else
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(e.Error.Message, "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void bgWorker_Xem_DoWork(object sender, DoWorkEventArgs e)
        {
            var dt = LayDuLieu(Query);

            myGridControl1.Invoke((Action)(() =>
            {
                myGridView1.Columns.Clear();
                myGridControl1.DataSource = dt;
            }));
        }

        private void bgWorker_Xem_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            myGridView1.HideLoadingPanel();
            if (e.Error != null)
            {
                DevExpress.XtraEditors.XtraMessageBox.Show(e.Error.Message, "Thông báo!", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
            }
        }

        private void btnStop_Click(object sender, EventArgs e)
        {
            if (bgWorker_Xem.IsBusy || bgWorker.IsBusy)
            {
                if (DevExpress.XtraEditors.XtraMessageBox.Show("Bạn có muốn hủy không?", "Xác nhận!", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == System.Windows.Forms.DialogResult.Yes)
                {
                    bgWorker_Xem.CancelAsync();
                    bgWorker.CancelAsync();
                }
            }
        }

        private void btnExp_Click(object sender, EventArgs e)
        {
            new VNPT.Utility.Grid.GridExport().Export(myGridControl1);
        }

    }
}